
package net.vinote.smart.platform.module.database.tool;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @see DatabaseMetaData
 * @author 郑俊伟
 * 
 */
public class DBTool
{
	private Connection conn;

	private DatabaseMetaData metaData;

	public DBTool(Connection conn)
	{
		try
		{
			this.conn = conn;
			if (conn == null)
			{
				throw new SQLException("Database Connection is null");
			}
			this.metaData = this.conn.getMetaData();
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}

	/**
	 * 获取此 Connection 对象的当前目录名称
	 * 
	 * @return
	 * @throws SQLException
	 */
	public String getCatalog() throws SQLException
	{
		return conn.getCatalog();
	}

	public String getSchema() throws SQLException
	{
		String schema = null;
		String dbName = metaData.getDatabaseProductName();
		if ("MySQL".equals(dbName))
		{
			schema = "";
		}
		else if ("Oracle".equals(dbName))
		{
			schema = metaData.getUserName();
		}
		return schema;
	}

	public List<String> getTables()
	{
		ResultSet rs = null;
		List<String> tableList = new ArrayList<String>();
		try
		{
			rs = conn.getMetaData().getTables(getCatalog(), getSchema(), null,
					new String[]
					{
						"TABLE"
					});
			while (rs.next())
			{
				tableList.add(rs.getString("TABLE_NAME"));
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if (rs != null)
			{
				try
				{
					rs.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return tableList;
	}

	/**
	 * @see DatabaseMetaData
	 * @param table
	 * @return
	 */
	public List<Column> getColumns(String table)
	{
		ResultSet rs = null;
		List<Column> columnList = new ArrayList<Column>();
		try
		{
			rs = conn.getMetaData().getColumns(getCatalog(), getSchema(),
					table, null);
			List<String> primaryKeys = getPrimaryKeys(table);
			int i = primaryKeys.size();
			while (rs.next())
			{
				Column column = new Column();
				column.setName(rs.getString("COLUMN_NAME"));
				column.setDataType(rs.getInt("DATA_TYPE"));
				column.setRemarks(rs.getString("REMARKS"));
				// 是否为主键
				if (i > 0)
				{
					for (String key : primaryKeys)
					{
						if (column.getName().equals(key))
						{
							column.setPrimaryKey(true);
							i--;
						}
					}
				}
				columnList.add(column);
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if (rs != null)
			{
				try
				{
					rs.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return columnList;
	}

	public List<String> getPrimaryKeys(String table)
	{
		ResultSet rs = null;
		List<String> keyList = new ArrayList<String>();
		try
		{
			rs = conn.getMetaData().getPrimaryKeys(getCatalog(), getSchema(),
					table);
			while (rs.next())
			{
				keyList.add(rs.getString("COLUMN_NAME"));
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			if (rs != null)
			{
				try
				{
					rs.close();
				}
				catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return keyList;
	}
}
